unit umain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, Inifiles, ComCtrls, RxMemDS, DBClient, StdCtrls,
  Spin, ExtCtrls, Grids, DBGrids, Provider, Menus, XPMan, DBCtrls, DBGridEh,
  UEdit;

type
  TForm1 = class(TForm)
    ADOCon: TADOConnection;
    ado_hum: TADOQuery;
    Panel1: TPanel;
    ado_data: TADOQuery;
    ds_hum: TDataSource;
    ds_data: TDataSource;
    Splitter1: TSplitter;
    CheckBox1: TCheckBox;
    qry1: TADOQuery;
    pnl1: TPanel;
    ado_datacard_sequ: TIntegerField;
    ado_datamoney: TBCDField;
    ado_databalance: TBCDField;
    ado_dataop_date: TDateTimeField;
    ado_datamachinetype: TIntegerField;
    XPManifest1: TXPManifest;
    Splitter2: TSplitter;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Memo1: TMemo;
    ProgressBar1: TProgressBar;
    PopupMenu1: TPopupMenu;
    Clear1: TMenuItem;
    PageControl2: TPageControl;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    qry_dataAfter: TADOQuery;
    IntegerField2: TIntegerField;
    BCDField1: TBCDField;
    BCDField2: TBCDField;
    DateTimeField1: TDateTimeField;
    IntegerField3: TIntegerField;
    dsafterdata: TDataSource;
    qry_dataAfterisAddBack: TIntegerField;
    Panel2: TPanel;
    ado_humret: TIntegerField;
    hum_memory: TRxMemoryData;
    DataSource2: TDataSource;
    PopupMenu2: TPopupMenu;
    N1: TMenuItem;
    ado_humremark: TStringField;
    Panel3: TPanel;
    DBGrid_afterdata: TDBGridEh;
    GroupBox1: TGroupBox;
    btnDBSet: TButton;
    edtendcardno: TEdit;
    edtbegcardno: TEdit;
    Label1: TLabel;
    dtp2: TDateTimePicker;
    lbl2: TLabel;
    dtp1: TDateTimePicker;
    lbl1: TLabel;
    Label2: TLabel;
    PageControl3: TPageControl;
    TabSheet4: TTabSheet;
    GroupBox2: TGroupBox;
    btAnalyse: TButton;
    Label3: TLabel;
    Label4: TLabel;
    cdsDelete: TADODataSet;
    cdsAddRec: TADOQuery;
    cdsBack: TADODataSet;
    ado_humempno: TIntegerField;
    ado_dataemp_id: TIntegerField;
    qry_dataAfteremp_id: TIntegerField;
    ado_datakind: TIntegerField;
    qry_dataAfterkind: TIntegerField;
    Button1: TButton;
    PMCons: TPopupMenu;
    Ndel: TMenuItem;
    ado_dataid: TIntegerField;
    qry_dataAfterid: TIntegerField;
    dgTable: TDBGridEh;
    CheckBox2: TCheckBox;
    Button2: TButton;
    N2: TMenuItem;
    N3: TMenuItem;
    ado_humcard_id: TIntegerField;
    ado_datacard_id: TIntegerField;
    ado_dataflag: TIntegerField;
    qry_dataAftercard_id: TIntegerField;
    qry_dataAfterflag: TIntegerField;
    TabSheet5: TTabSheet;
    Label5: TLabel;
    EdtFull: TEdit;
    EdtBack: TEdit;
    Label6: TLabel;
    Label7: TLabel;
    EdtCons: TEdit;
    EdtBan: TEdit;
    Label8: TLabel;
    Label9: TLabel;
    EdtExcept: TEdit;
    QryCount: TADOQuery;
    Label10: TLabel;
    EdtCorrect: TEdit;
    TabSheet6: TTabSheet;
    DBGridEh1: TDBGridEh;
    QryBak: TADOQuery;
    Dsbak: TDataSource;
    N4: TMenuItem;
    NAddCons: TMenuItem;
    NAddFull: TMenuItem;
    N8: TMenuItem;
    NEdit: TMenuItem;
    TabSheet7: TTabSheet;
    Allow_Grid: TDBGrid;
    DSAllow: TDataSource;
    QryAllow: TADOQuery;
    QryAllowid: TAutoIncField;
    QryAllowsys_card_no: TIntegerField;
    QryAllowmoney: TBCDField;
    QryAllowvalid_date: TDateTimeField;
    QryAllowbatch: TWideStringField;
    QryAllowis_transfer: TIntegerField;
    QryAllowis_ok: TIntegerField;
    QryAllowreceive_money: TBCDField;
    QryAllowreceive_date: TDateTimeField;
    QryAllowuser_id: TIntegerField;
    QryAllowis_pass: TIntegerField;
    QryAllowdate: TDateTimeField;
    PMAllow: TPopupMenu;
    N7: TMenuItem;
    DBGrid_data: TDBGridEh;
    procedure FormCreate(Sender: TObject);
    procedure btAnalyseClick(Sender: TObject);
    procedure ado_humRETGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure ado_humRETSetText(Sender: TField; const Text: String);
    procedure FormShow(Sender: TObject);
    procedure btnDBSetClick(Sender: TObject);
    procedure Clear1Click(Sender: TObject);
    procedure ado_datamachinetypeGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure dbgrd_humDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid2DblClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure IntegerField3GetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure btnDelDataClick(Sender: TObject);
    procedure DBGrid_afterdataDrawColumnCell(Sender: TObject;
      const Rect: TRect; DataCol: Integer; Column: TColumnEh;
      State: TGridDrawState);
    procedure Button1Click(Sender: TObject);
    procedure dgTableDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
    procedure DataSource2DataChange(Sender: TObject; Field: TField);
    procedure Button2Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure NEditClick(Sender: TObject);
    procedure NAddConsClick(Sender: TObject);
    procedure NAddFullClick(Sender: TObject);
    procedure QryAllowis_transferGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure QryAllowis_okGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure N7Click(Sender: TObject);
    procedure DBGrid_dataDblClick(Sender: TObject);
    procedure DBGrid_dataDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
  private
    { Private declarations }
    Constr :String;
    DbType :Integer;
    Errcount:Integer;
    Function InitData(strdate, enddate: TDateTime):Boolean;
    procedure AnalyseData;
    procedure ProcessData;
    procedure CountAll;
    function CheckData(card_id:string):string;
    procedure AddRepRecords(card_id: Integer; CardSequ: Integer; card_balance: Double; ConsumeMoney: Double);
    procedure AddChargeRecords(card_id: Integer; CardSequ: Integer);

  public
    { Public declarations }
  end;

//获取字符串参数配置
Function GetIniSet(Tit, Opt, def:String):String;
procedure WriteLog(log: string);
var
  Form1: TForm1;

implementation

uses UDbSet, Math, DateUtils, UHint, UAddRec;
{

                   (1,_(u'充值')),
                    (2,_(u'补贴')),
                    (5,_(u'退款')),
                    (13,_(u'充值优惠')),
                    (14,_(u'无卡退卡')),
                    
}

{$R *.dfm}

Function GetIniSet(Tit, Opt, def:String):String;
var
  Optini:TIniFile;
  res:String;
begin
  Optini := TIniFile.Create(ExtractFileDir(Application.ExeName)+'\setting.dat');
  res := Optini.ReadString(Tit, Opt, def);
  Result := res;
end;

procedure WriteLog(log: string);
var
  Myfile: TextFile;
  PathStr: string;
begin


  PathStr := ExtractFilePath(Paramstr(0)) + 'log\' + FormatDateTime('yyyyMMdd', now) + 'log.txt';
  AssignFile(Myfile, PathStr);
  try
    if FileExists(PathStr) = true then
      Append(MyFile)
    else
      Rewrite(Myfile);

    Writeln(MyFile, FormatDatetime('yyyy-MM-dd HH:mm:ss:zzz', now) + '   ' + log);

  finally
    CloseFile(Myfile);
  end;

end;


procedure TForm1.FormCreate(Sender: TObject);
var
   constr:String;
begin
   dtp1.Date := StartOfTheMonth(Now);
   dtp2.Date := EndOfTheMonth(Now);
   Memo1.Lines.Add('数据库未配置');
end;

procedure TForm1.btAnalyseClick(Sender: TObject);
begin
try
  if (edtbegcardno.Text = '') or (edtendcardno.Text = '') then
  begin
    ShowMessage('请输入卡号区间');
    Exit;
  end;
   btAnalyse.Enabled := False;
   Errcount := 0;
   if InitData(dtp1.Date, dtp2.Date) = False then
   begin
      ado_hum.Close;
      ado_data.Close;
      Exit;
   end;
   hum_memory.EmptyTable;
   hum_memory.LoadFromDataSet(ado_hum,ado_hum.RecordCount,lmAppend);
   TabSheet4.Caption := '双击显示处理结果('+inttostr(hum_memory.RecNo)+'/'+inttostr(hum_memory.RecordCount)+')';
   DBGrid_data.DataSource := nil;
   DBGrid_afterdata.DataSource := nil;
   AnalyseData();
   DBGrid_data.DataSource := dsafterdata;
   DBGrid_afterdata.DataSource := dsafterdata;
   CountAll();
finally
  btAnalyse.Enabled := True;
end;
end;

Function TForm1.InitData(strdate, enddate: TDateTime):Boolean;
var
  sqlstr:String;
begin
   Memo1.Lines.Add('加载人事数据......');

   with qry1 do
   begin
      sqlstr := 'select top 1 isAddBack from pos_icconsumerlist'; //
     try
       if Active then
          Close;
        SQL.Text := sqlstr;
        Open;
     except
        if Active then
          Close;
        SQL.Text := 'alter table pos_icconsumerlist add isAddBack int  ';
        ExecSQL;
     end;

     sqlstr := 'select top 1 isAddBack from pos_carcashsz'; //
     try
       if Active then
          Close;
        SQL.Text := sqlstr;
        Open;
     except
        if Active then
          Close;
        SQL.Text := 'alter table pos_carcashsz add isAddBack int  ';
        ExecSQL;

     end;

     sqlstr := 'update pos_carcashsz set isAddBack = 0 where  isAddBack = 4'; //
     try
        SQL.Text := sqlstr;
        ExecSQL;
     except
     end;
     sqlstr := 'update pos_icconsumerlist set isAddBack = 0 where  isAddBack = 4'; //
     try
        SQL.Text := sqlstr;
        ExecSQL;
     except
     end;
   end;

   with ado_hum do
   begin
      sqlstr := Format('select UserID_id as empno, sys_card_no as card_id, 0 as ret, ''√'' as remark from personnel_issuecard where (sys_card_no is not null) and (UserID_id is not null) and (sys_card_no >=%d) and (sys_card_no<=%d) order by sys_card_no',[strtoint(edtbegcardno.Text),strtoint(edtendcardno.Text)]); //
     try
        SQL.Text := sqlstr;
        Open;
        //dbgrd_hum.DataSource := ds_hum;
     except
        result := False;
        Exit;
     end;
   end;

   Memo1.Lines.Add('加载充值、消费数据......');
   Application.ProcessMessages;

  with ado_data do
  begin
    sqlstr := format('(select id,[user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,0 as machinetype from [pos_carcashsz] where type_id <> 5 and '
                    +'checktime >= ''%s'' and checktime <= ''%s'' and isnull(cardserial,0)<>0 and money > 0) '
                    +' union all (select id,[user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,1 as machinetype from [pos_carcashsz] '
                    +' where type_id=5 and checktime >= ''%s'' and checktime <= ''%s'' and isnull(cardserial,0)<>0  and money > 0)'
                    + 'union all (select id,[user_id] as emp_id, [sys_card_no]  as card_id, card_serial_num as card_sequ, pos_model as kind, 0 as flag, [money], balance, pos_time as op_date,1 as machinetype from [pos_icconsumerlist] '
                    +' where pos_time >= ''%s'' and pos_time <= ''%s'' and isnull(card_serial_num,0)<>0  and money > 0) '
                    + 'order by card_id, card_sequ, op_date', [DateToStr(strdate), DateToStr(enddate), DateToStr(strdate), DateToStr(enddate),DateToStr(strdate), DateToStr(enddate)]);

    //sqlstr := format('(select emp_id, card_id,card_sequ,kind,0 as flag, charge_money as money,card_balance as balance,op_date,0 as machinetype from mchargerecords where op_ymd >= ''%s'' and op_ymd <= ''%s'') '
            //        + 'union (select emp_id, card_id,card_sequ,kind,flag,card_consume as money, card_balance as balance,sign_time as op_date,1 as machinetype from mealrecords where op_ymd >= ''%s'' and op_ymd <= ''%s'') '
            //        + 'order by card_id, card_sequ, op_date', [DateToStr(strdate), DateToStr(enddate), DateToStr(strdate), DateToStr(enddate)]);

     try
        SQL.Text := sqlstr;
        Open;
     except
        result := False;
        Exit;
     end;
   end;
   Application.ProcessMessages;
   result := True;
end;

procedure TForm1.AnalyseData;
var
   mpos, i, rec, id,prvid,cardrec, prvcardrec, prvmach,mach,sequ_interval:Integer;
  prvban, ban, prvposm,posm: Double;
  card_id,str,str2: string;
  idatetime,prvdatetime,curdatetime:TDateTime;
  imoney,ipermoney,imodmoney,iposm,iban:Integer;
begin
  Memo1.Lines.Add('分析数据......');
  Application.ProcessMessages;
  with ado_hum do
  begin
    ProgressBar1.Min := 0;
    ProgressBar1.Step := 1;
    ProgressBar1.Max := RecordCount;
    ProgressBar1.Position := 0;
    Application.ProcessMessages;
    ado_hum.First;
    //ShowMessage(ado_hum.FieldByName('card_id').AsString);
    while not ado_hum.Eof do
    begin
      card_id := ado_hum.FieldByName('card_id').AsString;
      ado_data.Filtered := False;
      ado_data.Filter := 'card_id = ' + QuotedStr(card_id);
      ado_data.Filtered := True;
      if ado_data.IsEmpty then
      begin
        ProgressBar1.StepIt;// := mpos;
        if (mpos mod 100) = 0  then
          Application.ProcessMessages;
        Inc(mpos);
        Next;
        Continue;
      end;

      ado_data.First;
      //ShowMessage(ado_data.fieldbyname('card_sequ').AsString);;
      prvid := ado_data.FieldValues['id'];
      prvban := ado_data.FieldValues['balance'];
      prvcardrec := ado_data.FieldValues['card_sequ'];
      prvposm := ado_data.FieldValues['money'];
      prvdatetime := ado_data.FieldValues['op_date'];
      prvmach := ado_data.FieldValues['machinetype'];
      ado_data.Next;
      while not ado_data.Eof do
      begin
        id := ado_data.FieldValues['id'] ;
        cardrec := ado_data.FieldValues['card_sequ'];
        ban :=  ado_data.FieldValues['balance'];
        posm := ado_data.FieldValues['money'];
        mach := ado_data.FieldValues['machinetype'];
        curdatetime := ado_data.FieldValues['op_date'];

        sequ_interval := cardrec - prvcardrec-1;
        str := '';
        if sequ_interval < 0 then
        begin
             //有重复数据
          try
            if (mach = 1)  and (Abs(ban+posm-prvban) > 0.000001) then    //
            begin
              str := Format('update pos_icconsumerlist set isAddBack=4  where id=%d', [id]);

            end;
            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then //
            begin
              str := Format('update pos_carcashsz set isAddBack=4 where id=%d', [id]);
            end;

            str2 := '';
            if str <> '' then
            begin
              if (prvmach = 1)  then
              begin
                str2 := Format('update pos_icconsumerlist set isAddBack=4  where  id=%d',  [id]);
              end;
              if (prvmach = 0)  then
              begin
                str2 := Format('update pos_carcashsz set isAddBack=4 where  id=%d',  [id]);
              end;

              qry1.SQL.Text := str;
              qry1.ExecSQL;

              qry1.SQL.Text := str2;
              qry1.ExecSQL;
              //Memo1.Lines.Add('删除重复记录：'+str);
              //WriteLog('delete='+inttostr(mach)+' 卡号：'+inttostr(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;
            if str <> '' then
            begin
              hum_memory.Locate('card_id',card_id,[]);
              hum_memory.Edit;
              hum_memory.FieldByName('ret').Value := 4;
              hum_memory.Post;
            end;
          except
          end
        end
        else if sequ_interval = 0 then    //流水号连续，记录完整
        begin
             //金额异常
          if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then  //(not SameValue(ban-posm, prvban))
          begin
            if ban > prvban then
            begin
              str := Format('update pos_carcashsz set isAddBack=3 where  id=%d ',  [id]);
            end;
          end;
          if (mach = 1) and (Abs(ban+posm-prvban) > 0.000001) then  //not  ( SameValue(ban+posm , prvban))
          begin
              str := Format('update pos_icconsumerlist set isAddBack=3 where  id=%d ',  [id]);
          end ;

          try
            if str <> '' then
            begin
              qry1.SQL.Text := str;
              qry1.ExecSQL;

              hum_memory.Locate('card_id',card_id,[]);
              hum_memory.Edit;
              hum_memory.FieldByName('ret').Value := 3;
              hum_memory.Post;
             // Memo1.Lines.Add('更新金额记录：'+str);
              //WriteLog(str+'=='+inttostr(mach)+' 卡号：'+(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;
          except
          end;
        end
        else if sequ_interval >= 1 then  //记录异常
        begin
          idatetime := prvdatetime + (curdatetime - prvdatetime)/2 ;
          if (ban > prvban) then     //当前余额比上一笔余额大，被认为少充值记录
          begin
            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then  //当前记录为充值记录
            begin
                str := Format('update pos_carcashsz set isAddBack=2 where  id=%d', [id]);
            end ;
            if(mach = 1) and (Abs(ban+posm-prvban) > 0.000001) then    //当前记录为消费记录
            begin
                str := Format('update pos_icconsumerlist set isAddBack=2 where  id=%d', [id]);
            end;
          end
          else //少消费记录
          begin
           //当前记录为充值记录
            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then
            begin
              str := Format('update pos_carcashsz set isAddBack=2 where  id=%d',   [id]);
            end ;
           //当前记录为消费记录
            if (mach = 1) and  (Abs(ban+posm-prvban) > 0.000001)  then
            begin
                str := Format('update pos_icconsumerlist set isAddBack=2 where id=%d',  [id]);
            end;
          end;
          try
            if str <> '' then
            begin
              qry1.SQL.Text := str;
              qry1.ExecSQL;
              //Memo1.Lines.Add('添加记录：'+str);
             // WriteLog(str);
              //WriteLog('delete='+inttostr(mach)+' 卡号：'+inttostr(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;

            hum_memory.Locate('card_id',card_id,[]);
            hum_memory.Edit;
            hum_memory.FieldByName('ret').Value := 2;
            hum_memory.Post;
          except
          end;
        end ;
        prvid := ado_data.FieldValues['id'];
        prvban := ado_data.FieldValues['balance'];
        prvcardrec := ado_data.FieldValues['card_sequ'];
        prvposm := ado_data.FieldValues['money'];
        prvdatetime := ado_data.FieldValues['op_date'];
        prvmach := ado_data.FieldValues['machinetype'];
        ado_data.Next;
      end;
      ProgressBar1.StepIt;// := mpos;
      if (mpos mod 100) = 0  then
          Application.ProcessMessages;
      Inc(mpos);
      CheckData(card_id);
      Next;
    end;
    ado_data.Filtered := False;
  end;
  Memo1.Lines.Add('分析处理完毕!!');
end;

procedure TForm1.ado_humRETGetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
   if Sender.Value = 1 then
      Text := '×'
   else if Sender.Value = 0 then
      Text := '√'
   else
     Text := ' ';
end;

procedure TForm1.ado_humRETSetText(Sender: TField; const Text: String);
begin
  if Text = '1' then
    Sender.Value := 1
  else
    Sender.Value := 0;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
{   if FrmHint = nil then
   begin
      FrmHint := TFrmHint.Create(nil);
   end;
   FrmHint.ShowModal;    }
end;

procedure TForm1.btnDBSetClick(Sender: TObject);
begin
   if (FrmDbSet = nil) then
      FrmDbSet := TFrmDbSet.Create(nil);
   with FrmDbSet do begin
     if ShowModal = mrOK then
     begin
          DbType := rgtype.ItemIndex;
          if rgtype.ItemIndex=1 then
          begin
            if chkTrust.Checked then
              Constr :=  Format('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%s;Data Source=%s',
                                [cbbDBName.Text, edtServer.Text])
            else
              Constr :=  Format('Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=sa;Initial Catalog=%s;Data Source=%s',
                                [edtPWD.Text, cbbDBName.Text, edtServer.Text]) ;
          end
          else
          begin
              Constr :=  Format('Provider=MSDASQL.1;Persist Security Info=False;Data Source=odbcmysql;Extended Properties="DATABASE=%s;DSN=odbcmysql;OPTION=4104;PORT=0;SERVER=%s;STMT=SET NAMES ''GBK'';UID=root"',
                                [cbbDBName.Text, edtServer.Text]);
          end;
          try
             ADOCon.Connected := False;
             ADOCon.ConnectionString := Constr;
             ADOCon.Connected := True;
             Memo1.Lines.Add('数据库连接成功！！');
             btAnalyse.Enabled := True;
             Button1.Enabled := true;
          except
             Memo1.Lines.Add('数据库连接失败！！');
             btAnalyse.Enabled := False;
             Button1.Enabled := False;
          end;
     end;
   end;
end;

procedure TForm1.Clear1Click(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TForm1.ado_datamachinetypeGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.Value = 0 then
    Text := '充值';
  if Sender.Value = 1 then
  begin
    if ado_data.FieldByName('kind').AsInteger = 5 then
      Text := '退款'
    else
      Text := '消费';
  end;
end;

procedure TForm1.dbgrd_humDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  if (Sender as TDBGrid).DataSource.DataSet.FieldByName('ret').AsInteger = 3 then
   begin
      (Sender as TDBGrid).Canvas.Brush.Color:= clred;
      (Sender as TDBGrid).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGrid).DataSource.DataSet.FieldByName('ret').AsInteger = 4 then
   begin
      (Sender as TDBGrid).Canvas.Brush.Color:= clFuchsia;
      (Sender as TDBGrid).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGrid).DataSource.DataSet.FieldByName('ret').AsInteger = 2 then
   begin
      (Sender as TDBGrid).Canvas.Brush.Color:= clMoneyGreen;
      (Sender as TDBGrid).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
end;

procedure TForm1.DBGrid2DblClick(Sender: TObject);
var
   s, e:Integer;
  card,sqlstr,sqlallow:string;
begin
  card := hum_memory.FieldByName('card_id').AsString;
  edtbegcardno.Text :=  (card);
  edtendcardno.Text := (card);
  ado_data.Filtered := False;
  ado_data.Filter := 'card_id=' + card;
  ado_data.Filtered := True;

  ado_data.Last;
  e := ado_data.FieldByName('card_sequ').AsInteger;
  //Memo1.Lines.Add(format('最大流水号 %d, 记录条数 %d',[e, ado_data.RecordCount]));

  with qry_dataAfter do
  begin
    sqlstr := format('(select id, [user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,0 as machinetype,isaddback from [pos_carcashsz]'
                    +' where type_id <> 5 and checktime >= ''%s'' and checktime <= ''%s'' and sys_card_no=%d  and money > 0) '
                    +' union all (select id,[user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,1 as machinetype,isaddback from [pos_carcashsz]'
                    +' where type_id=5 and checktime >= ''%s'' and checktime <= ''%s'' and sys_card_no=%d  and money > 0) '
                    + 'union all (select id,[user_id] as emp_id, [sys_card_no]  as card_id, card_serial_num as card_sequ, pos_model as kind, 0 as flag, [money], balance, pos_time as op_date,1 as machinetype ,isaddback from [pos_icconsumerlist]'
                    +' where pos_time >= ''%s'' and pos_time <= ''%s'' and sys_card_no=%d  and money > 0) '
                   + 'order by sys_card_no, card_sequ, op_date', [DateToStr(dtp1.Date), DateToStr(dtp2.Date),StrToInt(card),DateToStr(dtp1.Date), DateToStr(dtp2.Date),StrToInt(card), DateToStr(dtp1.Date), DateToStr(dtp2.Date),StrToInt(card)]);
     try
       if Active then  close;
        SQL.Text := sqlstr;
        Open;
//        PageControl2.ActivePageIndex := 0;

      // MemoryData_after.CopyStructure(qry_dataAfter);
      // MemoryData_after.EmptyTable;
      // MemoryData_after.Open;
       //MemoryData_after.LoadFromDataSet(qry_dataAfter,qry_dataAfter.RecordCount,lmAppend);
       //DBGrid_afterdata.DataSource := dsafterdata;
     except
     end;
  end;
  with QryAllow do begin
      sqlallow := format('SELECT id, [user_id],[sys_card_no],[money],[valid_date],[batch],[is_pass],[is_transfer],[is_ok],[date],[receive_money],[receive_date] ' +
        ' FROM pos_allowance where sys_card_no = %d order by batch', [StrToInt(card)]);
      try
        if Active then  close;
        SQL.Text := sqlallow;
        Open;
      except
      end;
  end;
//
end;

procedure TForm1.N1Click(Sender: TObject);
begin
  if not hum_memory.Active then
    Exit;
  hum_memory.Next;
  while not hum_memory.Eof do
  begin
    if hum_memory.FieldByName('remark').AsString ='*' then
      Break
    else
      hum_memory.Next;

  end;

//  hum_memory.Filtered := False;
 // hum_memory.Filter := 'ret <> 0 and remark =''*'' ' ;
 // hum_memory.Filtered := True;
end;

function TForm1.CheckData(card_id: string):string;
var
  mach:Integer;
  prvban, ban, prvposm,posm: Double;
  sqlstr:string;
begin
  with qry_dataAfter do
  begin
    //sqlstr := format('(select emp_id, card_id,card_sequ,kind,0 as flag, charge_money as money,card_balance as balance,op_date,0 as machinetype ,isaddback from mchargerecords where op_ymd >= ''%s'' and op_ymd <= ''%s'' and card_id=%d) '
   //                 + 'union (select emp_id, card_id,card_sequ,kind,flag,card_consume as money, card_balance as balance,sign_time as op_date,1 as machinetype ,isaddback from mealrecords where op_ymd >= ''%s'' and op_ymd <= ''%s''  and card_id=%d) '
    //                + 'order by card_id, card_sequ, op_date', [DateToStr(dtp1.Date), DateToStr(dtp2.Date),card_id, DateToStr(dtp1.Date), DateToStr(dtp2.Date),card_id]);
    sqlstr := format('(select id,[user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,0 as machinetype ,isaddback from [pos_carcashsz] '
                    +' where type_id <> 5 and checktime >= ''%s'' and checktime <= ''%s'' and sys_card_no=%d and money >0) '
                    +'union all (select id, [user_id] as emp_id, [sys_card_no] as card_id, cardserial as card_sequ,[type_id] as kind, 0 as flag, [money], [blance] as balance,checktime as op_date,1 as machinetype ,isaddback from [pos_carcashsz] '
                    +' where type_id = 5 and checktime >= ''%s'' and checktime <= ''%s'' and sys_card_no=%d and money >0) '

                    + 'union all (select id, [user_id] as emp_id, [sys_card_no]  as card_id, card_serial_num as card_sequ, pos_model as kind, 0 as flag, [money], balance, pos_time as op_date,1 as machinetype ,isaddback from [pos_icconsumerlist] '
                    +' where pos_time >= ''%s'' and pos_time <= ''%s'' and sys_card_no=%d and money>0) '
                    + 'order by card_id, card_sequ, op_date', [DateToStr(dtp1.Date), DateToStr(dtp2.Date), StrToInt(card_id),DateToStr(dtp1.Date), DateToStr(dtp2.Date),StrToInt(card_id),DateToStr(dtp1.Date), DateToStr(dtp2.Date), StrToInt(card_id)]);

    try
      if Active then
         close;
      SQL.Text := sqlstr;
      Open;


      DisableControls;
      First;
      prvban := FieldValues['balance'];
      prvposm := FieldValues['money'];
      Next;
      while not Eof do
      begin
        ban :=  FieldValues['balance'];
        posm := FieldValues['money'];
        mach := FieldValues['machinetype'];

        if (mach = 1) and (Abs(ban+posm-prvban) > 0.000001)  then
        begin
           hum_memory.Locate('card_id',card_id,[]);
           hum_memory.Edit;
           hum_memory.FieldByName('remark').Value := '*';
           hum_memory.Post;
           Break;
        end;
        if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then
        begin
          hum_memory.Locate('card_id',card_id,[]);
          hum_memory.Edit;
          hum_memory.FieldByName('remark').Value := '*';
          hum_memory.Post;
          Break;
        end;
        prvban := FieldValues['balance'];
        prvposm := FieldValues['money'];
        Next;
      end;

      EnableControls;
    except

    end;
  end;
end;

procedure TForm1.IntegerField3GetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
  if Sender.Value = 0 then
    Text := '充值';
  if Sender.Value = 1 then
  begin
    if qry_dataAfter.FieldByName('kind').AsInteger = 5 then
      Text := '退款'
    else
      Text := '消费';
  end;
end;

procedure TForm1.btnDelDataClick(Sender: TObject);
var
  mach,card_id:Integer;
  str:string;
begin
  if Application.MessageBox(Pchar('确认删除该记录吗？'), Pchar('询问'), MB_YESNO or MB_ICONQUESTION) = IDYES then
  begin
    mach := dsafterdata.DataSet.fieldbyname('machinetype').AsInteger;
    try
      if (mach = 1)  then
      begin
        str := Format('delete from pos_icconsumerlist where id =%d',
                          [dsafterdata.DataSet.FieldByName('id').AsInteger]);
      end;
      if (mach = 0)  then
      begin
        str := Format('delete from pos_carcashsz where  id =%d',
                          [dsafterdata.DataSet.FieldByName('id').AsInteger]);
      end;

      qry1.SQL.Text := str;
      qry1.ExecSQL;
      Memo1.Lines.Add('删除重复记录：'+str);
      WriteLog('delete='+inttostr(mach)+' 卡号：'+inttostr(dsafterdata.DataSet.FieldByName('emp_id').AsInteger)+'流水号：'+inttostr(dsafterdata.DataSet.FieldByName('card_sequ').AsInteger)+'时间：'+dsafterdata.DataSet.FieldByName('op_date').AsString);

      ShowMessage('删除成功,请双击刷新');
    except
      on err:Exception do
        ShowMessage(err.Message);
    end;
  end;
end;

procedure TForm1.DBGrid_afterdataDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumnEh;
  State: TGridDrawState);
begin
if qry_dataAfter.FieldByName('isaddback').AsInteger = 2 then
   begin
      DBGrid_afterdata.Canvas.Brush.Color:= clMoneyGreen;
      DBGrid_afterdata.DefaultDrawColumnCell(rect,datacol,column,state);
   end;

   if qry_dataAfter.FieldByName('isAddBack').AsInteger = 3 then
   begin
      DBGrid_afterdata.Canvas.Brush.Color:= clred;
      DBGrid_afterdata.DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if qry_dataAfter.FieldByName('isAddBack').AsInteger = 4 then
   begin
      DBGrid_afterdata.Canvas.Brush.Color:= clFuchsia;
      DBGrid_afterdata.DefaultDrawColumnCell(rect,datacol,column,state);
   end;
end;

procedure TForm1.AddRepRecords(card_id, CardSequ: Integer;
  card_balance, ConsumeMoney: Double);
var
  sqlstr, Sign_Time: string;
  pos_sequ: integer;
begin
  try

    cdsDelete.Close;
    cdsDelete.CommandText := ' select * from mealrecords_bak where flag in (1,2,3,5,6,9,90,103) and card_id= '
      + IntToStr(card_id) + ' and card_sequ = ' + IntToStr(CardSequ)
      + ' and card_balance = ' + FloatToStr(card_balance + ConsumeMoney);
    cdsDelete.Open;


    if cdsDelete.RecordCount >= 1 then
    begin

      cdsDelete.Last;

      Sign_Time := FormatDateTime('yyyy-MM-dd HH:mm:ss', cdsDelete.FieldByName('sign_time').AsDateTime);
      pos_sequ := cdsDelete.FieldByName('pos_sequ').AsInteger;


      sqlstr := 'insert into mealrecords( ver_id ,pos_sequ , card_sequ ,opcard_id,clock_id,emp_id,sys_id ,card_id,sign_time,mark ,flag,card_times ,card_consume ,card_balance , '
        + ' kind ,op_ymd , op_user ,empname,deptno,deptname,isAddBack) '
        + ' select ver_id ,pos_sequ ,card_sequ ,opcard_id,clock_id,emp_id,sys_id ,card_id,sign_time,mark ,flag,card_times ,card_consume ,card_balance ,'
        + ' kind ,op_ymd,op_user ,empname,deptno,deptname, 2 isAddBack '
        + ' from mealrecords_bak  where flag in (1,2,3,5,6,9,90,103) and card_id= ' + IntToStr(card_id)
        + ' and card_sequ = ' + IntToStr(CardSequ)
        + ' and card_balance = ' + FloatToStr(card_balance + ConsumeMoney)
        + ' and sign_time = ' + QuotedStr(Sign_Time) + ' and pos_sequ = ' + IntToStr(pos_sequ)
        ;

      cdsAddRec.Close;
      cdsAddRec.SQL.text := sqlstr;
      cdsAddRec.ExecSQL;
    end;
  except
  end;
end;


procedure TForm1.AddChargeRecords(card_id, CardSequ: Integer);
var
  sqlstr,Sql_Fun_isNull: string;
  pos_sequ: integer;
begin
  try
    if DbType = 1 then
      Sql_Fun_isNull := 'isnull';

    if DbType = 0 then
      Sql_Fun_isNull := 'ifnull';

    cdsDelete.Close;
    cdsDelete.CommandText := ' select * from mchargerecords_bak where  card_id= '
      + IntToStr(card_id) + ' and card_sequ = ' + IntToStr(CardSequ) + ' and ' + Sql_Fun_isNull + '( charge_ver , 0 ) <> 0 ';
    cdsDelete.Open;

    if cdsDelete.RecordCount >= 1 then
    begin

      cdsDelete.Last;
      sqlstr := 'insert into mchargerecords(emp_id,sys_id,card_id,charge_money,card_balance,card_times,card_sequ,Kind,op_date,op_ymd,remark,difine_sequ,clock_id,opcard_id,empname,deptno,deptname)'
        + ' select emp_id,sys_id,card_id,charge_money,card_balance,card_times,card_sequ,Kind,op_date,op_ymd,remark,difine_sequ,clock_id,opcard_id,empname,deptno,deptname'
        + ' from mchargerecords_bak  where charge_money > 0 and card_id= ' + IntToStr(card_id)
        + ' and card_sequ = ' + IntToStr(CardSequ)
        ;

      cdsAddRec.Close;
      cdsAddRec.SQL.text := sqlstr;
      cdsAddRec.ExecSQL;

      sqlstr := format('update mallowance set charged=1,charge_money=%f,card_sequ=%d,card_balance=%f,clock_id=%d,charge_date=''%s'' where sys_id=%d and charge_ver=%d',
        [cdsDelete.FieldByName('charge_money').AsFloat,
        cdsDelete.FieldByName('card_sequ').AsInteger,
          cdsDelete.FieldByName('card_balance').AsFloat,
          cdsDelete.FieldByName('clock_id').AsInteger,
          formatdatetime('yyyy-MM-dd HH:mm:ss', cdsDelete.FieldByName('op_date').AsDateTime),
          cdsDelete.FieldByName('sys_id').AsInteger,
          cdsDelete.FieldByName('charge_ver').AsInteger]);


      cdsAddRec.Close;
      cdsAddRec.SQL.text := sqlstr;
      cdsAddRec.ExecSQL;

    end;


  except
  end;

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
try
  if (edtbegcardno.Text = '') or (edtendcardno.Text = '') then
  begin
    ShowMessage('请输入卡号区间');
    Exit;
  end;
  if Application.MessageBox(Pchar('处理前请务必备份数据库，确定备份了吗？'), Pchar('警告'), MB_YESNO or MB_ICONQUESTION) = IDno then
    Exit;

   btAnalyse.Enabled := False;
   if InitData(dtp1.Date, dtp2.Date) = False then
   begin
      ado_hum.Close;
      ado_data.Close;
      Exit;
   end;
   hum_memory.EmptyTable;
   hum_memory.LoadFromDataSet(ado_hum,ado_hum.RecordCount,lmAppend);
   DBGrid_data.DataSource := nil;
   DBGrid_afterdata.DataSource := nil;
   ProcessData();
   DBGrid_data.DataSource := ds_data;
  DBGrid_afterdata.DataSource := dsafterdata;
finally
  btAnalyse.Enabled := True;
end;
end;

procedure TForm1.ProcessData;
var
  mpos, i, rec, id,prvid,cardrec, prvcardrec, prvmach,mach,sequ_interval:Integer;
  prvban, ban, prvposm,posm: Double;
  card_id,str,str2: string;
  idatetime,prvdatetime,curdatetime:TDateTime;
  imoney,ipermoney,imodmoney,iposm,iban:Integer;
begin
  Memo1.Lines.Add('平帐处理开始......');
  Application.ProcessMessages;
  with ado_hum do
  begin
    ProgressBar1.Min := 0;
    ProgressBar1.Step := 1;
    ProgressBar1.Max := RecordCount;
    ProgressBar1.Position := 0;
    Application.ProcessMessages;
    ado_hum.First;
    while not ado_hum.Eof do
    begin
      card_id := ado_hum.FieldByName('card_id').AsString;
      ado_data.Filtered := False;
      ado_data.Filter := 'card_id = ' + QuotedStr(card_id);
      ado_data.Filtered := True;
      if ado_data.IsEmpty then
      begin
        ProgressBar1.StepIt;// := mpos;
        if (mpos mod 100) = 0  then
          Application.ProcessMessages;
        Inc(mpos);
        Next;
        Continue;
      end;

      ado_data.First;
      prvid :=  ado_data.FieldValues['id'];
      prvban := ado_data.FieldValues['balance'];
      prvcardrec := ado_data.FieldValues['card_sequ'];
      prvposm := ado_data.FieldValues['money'];
      prvdatetime := ado_data.FieldValues['op_date'];
      prvmach := ado_data.FieldValues['machinetype'];
      ado_data.Next;
      while not ado_data.Eof do
      begin
        id :=  ado_data.FieldValues['id'];
        cardrec := ado_data.FieldValues['card_sequ'];
        ban :=  ado_data.FieldValues['balance'];
        posm := ado_data.FieldValues['money'];
        mach := ado_data.FieldValues['machinetype'];
        curdatetime := ado_data.FieldValues['op_date'];

        sequ_interval := cardrec - prvcardrec-1;
        str := '';
        if sequ_interval < 0 then
        begin
          //有重复数据
          try
            if (mach = 1)  and (Abs(ban+posm-prvban) > 0.000001) then    //
            begin
              if CheckBox2.Checked  then
                str := Format('delete from pos_icconsumerlist where  id=%d',
                                [id])
              else
                str := Format('update pos_icconsumerlist set isAddBack=4  where  id=%d',
                                [id]);

            end;
            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then //
            begin
              if CheckBox2.Checked  then
                str := Format('delete from pos_carcashsz where  id=%d',
                                [id])
              else
                str := Format('update pos_carcashsz set isAddBack=4 where  id=%d',
                                [id]);
            end;

            str2 := '';
            if str <> '' then
            begin
              if not CheckBox2.Checked  then
              begin
                if (prvmach = 1)  then
                begin
                  str2 := Format('update pos_icconsumerlist set isAddBack=4  where  id=%d',
                                    [id]);
                end;
                if (prvmach = 0)  then
                begin
                  str2 := Format('update pos_carcashsz set isAddBack=4 where  id=%d',
                                    [id]);
                end;
              end;

              if str <> '' then
              begin
                qry1.SQL.Text := str;
                qry1.ExecSQL;
              end;
              if str2 <> '' then
              begin
                qry1.SQL.Text := str2;
                qry1.ExecSQL;
              end;
              Memo1.Lines.Add('删除重复记录：'+str);
              WriteLog('delete='+inttostr(mach)+' 卡号：'+(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;
            if str <> '' then
            begin
              hum_memory.Locate('card_id',card_id,[]);
              hum_memory.Edit;
              hum_memory.FieldByName('ret').Value := 4;
              hum_memory.Post;
            end;
          except
          end
        end
        else if sequ_interval = 0 then    //流水号连续，记录完整
        begin
             //金额异常
          if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then  //(not SameValue(ban-posm, prvban))
          begin
            if ban > prvban then
            begin
              str := Format('update pos_carcashsz set isAddBack=3 where  id=%d',  //money=%f,
                              [ban-prvban,id]);
            end;
          end;
          if (mach = 1) and (Abs(ban+posm-prvban) > 0.000001) then  //not  ( SameValue(ban+posm , prvban))
          begin
            if ban < prvban then
            begin
              str := Format('update pos_icconsumerlist set isAddBack=3 where  id=%d', //  money=%f,
                              [prvban-ban,id]);
            end
            else
            begin
              //str := Format('delete from pos_icconsumerlist  where card=''%s'' and card_sequ = %d ',[card_id,cardrec]);
            end;
          end ;

          try
            if str <> '' then
            begin
              qry1.SQL.Text := str;
              qry1.ExecSQL;

              hum_memory.Locate('card_id',card_id,[]);
              hum_memory.Edit;
              hum_memory.FieldByName('ret').Value := 3;
              hum_memory.Post;
              Memo1.Lines.Add('更新金额记录：'+str);
              WriteLog(str+'=='+inttostr(mach)+' 卡号：'+(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;
          except
          end;
        end
        else if sequ_interval >= 1 then  //记录异常
        begin
          idatetime := prvdatetime + (curdatetime - prvdatetime)/2 ;
          if (ban > prvban) then     //当前余额比上一笔余额大，被认为少充值记录
          begin

            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then  //当前记录为充值记录
            begin
              if ban-posm-prvban > 0 then
              begin
                  if DbType = 1 then    //从充值表--->充值表
                  begin                                       //FormatDateTime('yyyy-mm-dd hh:MM:ss',idatetime),
                      str := Format('insert into pos_carcashsz (user_id,card ,money, blance, cardserial, checktime, type_id,isAddBack, '
                              +'dept_id,sn_name,change_time,create_operator,create_time,status,sys_card_no,discount,hide_column,log_flag)'

                              +' select user_id, card, %f, %f,  cardserial-1,  DATEADD(hour, -1, checktime),  1, 2,'
                              +'dept_id,sn_name,change_time,create_operator,create_time,status,sys_card_no,discount,1,3 from pos_carcashsz where  id=%d',
                              [(ban-posm)-prvban, ban-posm,id])
                  end;
              end 
              else
              begin
                  if DbType = 1 then     //从充值表--->消费表
                  begin
                    str := Format('insert into pos_icconsumerlist (user_id,user_pin,money,balance, user_name, dept_id, card,sys_card_no,'
                              +'dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,type_name,pos_model,dining_id,meal_id,'
                              +'meal_data,create_operator,log_flag,discount,isAddBack)'

                              +' select user_id, b.badgenumber,%f, %f,b.name,b.defaultdeptid,card,sys_card_no,'
                              +'sn_name,cardserial-1,sn_name, DATEADD(hour, -1, checktime),convey_time ,6,2,1,2,'
                              +'DATEADD(hour, -1, checktime),0,3,discount,2 from pos_carcashsz a ,userinfo b '
                              +' where (a.user_id=b.userid) and (card=''%s'' and cardserial = %d)',
                            [prvban-(ban-posm), ban-posm,ado_data.FieldByName('card_id').AsString, cardrec]);
                  end;
              end;
            end ;
            if(mach = 1) and (Abs(ban+posm-prvban) > 0.000001) then    //当前记录为消费记录
            begin
                if DbType = 1 then   //从消费表--->充值表
                begin
                  str := Format('insert into pos_carcashsz (user_id,card ,money, blance, cardserial, checktime, type_id,isAddBack, '
                              +'dept_id,sn_name,change_time,create_time,status,sys_card_no,discount,hide_column,log_flag)'

                              +' select user_id, card, %f, %f, card_serial_num-1,  DATEADD(hour, -1, pos_time),  1, 2,'
                              +'dept_id,dev_sn,convey_time,convey_time,0,sys_card_no,discount,1,3 '
                              +'from pos_icconsumerlist where  id=%d',
                        [(ban+posm)-prvban, ban+posm,id])
                end;
            end;
          end
          else //少消费记录
          begin


           //当前记录为充值记录
            if (mach = 0) and (Abs(ban-posm-prvban) > 0.000001) then
            begin
                if DbType = 1 then   //从充值表--->消费表
                begin
                   str := Format('insert into pos_icconsumerlist (user_id,user_pin,money,balance, user_name, dept_id, card,sys_card_no,'
                              +'dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,type_name,pos_model,dining_id,meal_id,'
                              +'meal_data,create_operator,log_flag,discount,isAddBack)'

                              +' select user_id, b.badgenumber,%f, %f,b.name,b.defaultdeptid,card,sys_card_no,'
                              +'sn_name,cardserial-1,sn_name, DATEADD(hour, -1, checktime),convey_time ,6,2,1,2,'
                              +'DATEADD(hour, -1, checktime),0,3,discount,2 from pos_carcashsz a ,userinfo b '
                              +' where (a.user_id=b.userid) and (card=''%s'' and cardserial = %d)',
                            [prvban-(ban-posm), ban-posm,ado_data.FieldByName('card_id').AsString, cardrec])
                end;

            end ;
               //当前记录为消费记录
            if (mach = 1) and  (Abs(ban+posm-prvban) > 0.000001)  then
            begin
              if prvban > (ban+posm) then
              begin
                if DbType = 1 then  //从消费表--->消费表
                  begin
                      str := Format('insert into pos_icconsumerlist (user_id,user_pin,money,balance, user_name, dept_id, card,sys_card_no,'
                              +'dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,type_name,pos_model,dining_id,meal_id,'
                              +'meal_data,create_operator,log_flag,discount,isAddBack)'

                              +' select user_id,user_pin,%f, %f, user_name, dept_id, card,sys_card_no,'
                              +'dev_sn,card_serial_num-1,dev_serial_num,DATEADD(minute, -1, pos_time),convey_time,type_name,pos_model,dining_id,meal_id,'
                              +'meal_data,create_operator,log_flag,discount,2 '
                              +'from pos_icconsumerlist where  id=%d',
                              [prvban-(ban+posm), ban+posm,id])
                  end;
              end
              else
              begin
                  if DbType = 1 then   //从消费表--->充值表
                  begin
                    str := Format('insert into pos_carcashsz (user_id,card ,money, blance, cardserial, checktime, type_id,isAddBack, '
                              +'dept_id,sn_name,change_time,create_time,status,sys_card_no,discount,hide_column,log_flag)'

                              +' select user_id, card, %f, %f, card_serial_num-1,  DATEADD(hour, -1, pos_time),  1, 2,'
                              +'dept_id,dev_sn,convey_time,convey_time,0,sys_card_no,discount,1,3 '
                              +'from pos_icconsumerlist where  id=%d',
                        [(ban+posm)-prvban, ban+posm,id])  ;
                  end;
              end;
            end;
          end;
          try
            if str <> '' then
            begin
              qry1.SQL.Text := str;
              qry1.ExecSQL;
              Memo1.Lines.Add('添加记录：'+str);
              WriteLog(str);
              //WriteLog('delete='+inttostr(mach)+' 卡号：'+inttostr(card_id)+'流水号：'+inttostr(cardrec)+'金额：'+floattostr(posm)+'余额：'+floattostr(ban)+'时间：'+datetimetostr(curdatetime));
            end;

            hum_memory.Locate('card_id',card_id,[]);
            hum_memory.Edit;
            hum_memory.FieldByName('ret').Value := 2;
            hum_memory.Post;
          except
          end;
        end ;
        prvid :=  ado_data.FieldValues['id'];
        prvban := ado_data.FieldValues['balance'];
        prvcardrec := ado_data.FieldValues['card_sequ'];
        prvposm := ado_data.FieldValues['money'];
        prvdatetime := ado_data.FieldValues['op_date'];
        prvmach := ado_data.FieldValues['machinetype'];
        ado_data.Next;
      end;
      ProgressBar1.StepIt;// := mpos;
      if (mpos mod 100) = 0  then
          Application.ProcessMessages;
      Inc(mpos);
      CheckData(card_id);
      Next;
    end;
    ado_data.Filtered := False;
  end;
  Memo1.Lines.Add('平帐处理完毕!!');
end;

procedure TForm1.dgTableDrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
begin
 if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('ret').AsInteger = 3 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clred;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('ret').AsInteger = 4 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clFuchsia;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('ret').AsInteger = 2 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clMoneyGreen;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
end;

procedure TForm1.DataSource2DataChange(Sender: TObject; Field: TField);
begin
  TabSheet4.Caption := '双击显示处理结果('+inttostr(hum_memory.RecNo)+'/'+inttostr(hum_memory.RecordCount)+')';
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  edtbegcardno.Text := '0';
  edtendcardno.Text := '999999999'
end;

procedure TForm1.N2Click(Sender: TObject);
begin
  if hum_memory.Active then
    hum_memory.EmptyTable;
end;

procedure TForm1.CountAll;
var
  a1,a2,a3,a4,a5:Double;
begin
   with QryCount do begin
      Close;
      SQL.Text := 'select SUM(money) as sfull from pos_carcashsz where hide_column in (1,2,13)';       //充值
      Open();
      a1:=0;
      if FieldValues['sfull'] <> NULL then
      begin
        a1 := FieldValues['sfull'];
      end;
      EdtFull.Text := FloatToStr(a1);

      Close;
      SQL.Text := 'select SUM(money) as sback from pos_carcashsz where hide_column in (5,14)';    //退款
      Open();
      a2:=0;
      if FieldValues['sback'] <> NULL then
        a2 := FieldValues['sback'];
      EdtBack.Text := FloatToStr(a2);

      Close;
      SQL.Text := 'select SUM(money) as scons from [pos_icconsumerlist] where pos_model <> 9';
      Open();
      a3:=0;
      if FieldValues['scons'] <> NULL then
        a3 :=  FieldValues['scons'];
      EdtCons.Text :=  FloatToStr(a3);

      Close;
      SQL.Text := 'select SUM(money) as sCorrect from [pos_icconsumerlist] where pos_model = 9';
      Open();
      a4:=0;
      if FieldValues['sCorrect'] <> NULL  then
        a4 := FieldValues['sCorrect'];
      EdtCorrect.Text :=  FloatToStr(a4);

      Close;
      SQL.Text := 'select SUM(blance) as sBlance from  dbo.personnel_issuecard';
      Open();
      a5:=0;
      if FieldValues['sBlance'] <> NULL then
        a5 := FieldValues['sBlance'];
      EdtBan.Text := FloatToStr(a5);

      EdtExcept.Text := FormatFloat('0.00', a1 - a2 - a3 - a5 + a4);
   end;
end;

procedure TForm1.NEditClick(Sender: TObject);
var
    mach, id, i:Integer;
    str:String;
begin
    id := dsafterdata.DataSet.fieldbyname('id').AsInteger;
    mach := dsafterdata.DataSet.fieldbyname('machinetype').AsInteger;

    with FrmEdit do begin
        EdtCard.Enabled := False;
        EdtSequ.Enabled := False;
        if ShowModal = MROK then
        begin
          try
            if (mach = 1)  then
            begin
              str := Format('update pos_icconsumerlist set [money]=%s, balance = %s where id =%d',  [FrmEdit.EdtMoney.Text, FrmEdit.EdtBanlance.Text , id]);
            end;
            if (mach = 0)  then
            begin
              str := Format('update pos_carcashsz  set [money]=%s, blance = %s  where id =%d',  [FrmEdit.EdtMoney.Text,FrmEdit.EdtBanlance.Text, id]);
            end;

            qry1.SQL.Text := str;
            qry1.ExecSQL;

            qry_dataAfter.Close;
            qry_dataAfter.Open;
            Memo1.Lines.Add('修改记录：'+str);
            WriteLog('update  卡号：'+inttostr(dsafterdata.DataSet.FieldByName('emp_id').AsInteger)+'流水号：'+inttostr(dsafterdata.DataSet.FieldByName('card_sequ').AsInteger)+'时间：'+dsafterdata.DataSet.FieldByName('op_date').AsString);
          except
            on err:Exception do
              ShowMessage(err.Message);
          end;
        end;
    end;
end;

procedure TForm1.NAddConsClick(Sender: TObject);
var
    mach, id, i:Integer;
    str:String;
begin
    id := dsafterdata.DataSet.fieldbyname('id').AsInteger;
    mach := dsafterdata.DataSet.fieldbyname('machinetype').AsInteger;

    with FrmEdit do begin
        qry_dataAfter.DisableControls;
        EdtCard.Enabled := False;
        EdtSequ.Text := IntToStr(dsafterdata.DataSet.fieldbyname('card_sequ').AsInteger + 1);
        if ShowModal = MROK then
        begin
         try
            str := format('INSERT INTO  pos_icconsumerlist ([user_id],user_pin ,[user_name] ,[dept_id] ,[card] ' +
           ',[sys_card_no] ,[dev_sn]  ,[card_serial_num] ,[dev_serial_num] ,[pos_time] ,[convey_time] ,[type_name] ,[money] ,[balance] ' +
           ',[pos_model] ,[dining_id] ,[meal_id] ,[meal_data] ,[create_operator],[log_flag] ,[discount] ,[isAddBack]) '+
           ' select [user_id], user_pin, [user_name], [dept_id], [card], [sys_card_no], [dev_sn], %s, [dev_serial_num], [pos_time] ' +
           ',[convey_time], [type_name], %s, %s, [pos_model], [dining_id], [meal_id], [meal_data], [create_operator], [log_flag], [discount], 99 ' +
           ' from pos_icconsumerlist where id=%d ', [FrmEdit.EdtSequ.Text,FrmEdit.EdtMoney.Text, FrmEdit.EdtBanlance.Text, id]);
            qry1.SQL.Text := str;
            qry1.ExecSQL;

            qry_dataAfter.Close;
            qry_dataAfter.Open;
            Memo1.Lines.Add('新增充值记录：'+str);
            WriteLog('insert= '+inttostr(mach)+' 卡号：'+inttostr(dsafterdata.DataSet.FieldByName('emp_id').AsInteger)+'流水号：'+inttostr(dsafterdata.DataSet.FieldByName('card_sequ').AsInteger)+'时间：'+dsafterdata.DataSet.FieldByName('op_date').AsString);
          except
            on err:Exception do
              ShowMessage(err.Message);
          end;
          qry_dataAfter.EnableControls;
        end;
    end;
end;

procedure TForm1.NAddFullClick(Sender: TObject);
var
    mach, id, i:Integer;
    str:String;
begin
    id := dsafterdata.DataSet.fieldbyname('id').AsInteger;
{    mach := qry_dataAfter.fieldbyname('machinetype').AsInteger;
    if (mach = 0) then
    begin
      ShowMessage('请选择一条附近的消费记录');
      Exit;
    end;   }
    with FrmEdit do begin
        qry_dataAfter.DisableControls;
        EdtCard.Enabled := False;
        EdtSequ.Text := IntToStr(dsafterdata.DataSet.fieldbyname('card_sequ').AsInteger + 1);
        if ShowModal = MROK then
        begin
         try
            str := format('INSERT INTO  pos_carcashsz ([user_id], [card], [sys_card_no], cardserial, checktime, type_id  ' +
           ', [money], [blance], [isAddBack]) select [user_id], [card], [sys_card_no], %s, [pos_time], 1,  %s, %s, 99 ' +
           ' from pos_icconsumerlist where id=%d ', [FrmEdit.EdtSequ.Text, FrmEdit.EdtMoney.Text, FrmEdit.EdtBanlance.Text, id]);
            qry1.SQL.Text := str;
            qry1.ExecSQL;

            qry_dataAfter.Close;
            qry_dataAfter.Open;
            Memo1.Lines.Add('新增消费记录：'+str);
            WriteLog('update= '+inttostr(mach)+' 卡号：'+inttostr(dsafterdata.DataSet.FieldByName('emp_id').AsInteger)+'流水号：'+inttostr(dsafterdata.DataSet.FieldByName('card_sequ').AsInteger)+'时间：'+dsafterdata.DataSet.FieldByName('op_date').AsString);
          except
            on err:Exception do
              ShowMessage(err.Message);
          end;
          qry_dataAfter.EnableControls;
        end;
    end;
end;

procedure TForm1.QryAllowis_transferGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
    if Sender.AsInteger = 0 then
      Text := ' '
    else if Sender.AsInteger = 1 then
      Text := '√'
end;

procedure TForm1.QryAllowis_okGetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
    if Sender.AsInteger = 0 then
      Text := ' '
    else if Sender.AsInteger = 1 then
      Text := '√'
end;

procedure TForm1.N7Click(Sender: TObject);
var
    id:Integer;
    m, str :String;
begin
   id := QryAllow.fieldbyname('id').AsInteger;
   m :=  QryAllow.fieldbyname('money').AsString;
    try
      str := Format('update pos_allowance set is_ok=1, receive_money=money, receive_date = getdate() where id =%d',  [id]);
      qry1.SQL.Text := str;
      qry1.ExecSQL;

      QryAllow.Close;
      QryAllow.Open;
      Memo1.Lines.Add('修改补贴标记：'+str);
 //     WriteLog('update  卡号：'+inttostr(dsafterdata.DataSet.FieldByName('emp_id').AsInteger)+'流水号：'+inttostr(dsafterdata.DataSet.FieldByName('card_sequ').AsInteger)+'时间：'+dsafterdata.DataSet.FieldByName('op_date').AsString);
    except
      on err:Exception do
        ShowMessage(err.Message);
    end;
end;

procedure TForm1.DBGrid_dataDblClick(Sender: TObject);
begin
  ShowMessage(inttostr(qry_dataAfter.FieldByName('id').AsInteger));
end;

procedure TForm1.DBGrid_dataDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumnEh;
  State: TGridDrawState);
begin
   if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('isaddback').AsInteger = 2 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clMoneyGreen;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('isaddback').AsInteger = 3 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clred;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
   if (Sender as TDBGridEh).DataSource.DataSet.FieldByName('isaddback').AsInteger = 4 then
   begin
      (Sender as TDBGridEh).Canvas.Brush.Color:= clFuchsia;
      (Sender as TDBGridEh).DefaultDrawColumnCell(rect,datacol,column,state);
   end;
end;

end.

